# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
# import webspider.mysql.mysql as mysql
from webspider.items import WebspiderItem
import pymysql

<<<<<<< HEAD
=======

>>>>>>> a14f5150eb56dfc65bb96f2ac8b48776d600553f
class WebspiderPipeline(object):
    def process_item(self, item, spider):
        if spider.name == "51job":
            if item['salary'] is not None:
<<<<<<< HEAD
                item = self.process_content(item)
                self.save_to_mysql(spider, item)
            print(item)
        elif spider.name == "job_all":
            self.save_to_mysql(spider, item)
            # print(item)
        elif spider.name == 'job_c':
            if item['salary'] is not None:
                item = self.process_content(item)
                self.save_to_mysql(spider,item)
            print(item)
        return item

    # 对内容进行清洗
    def process_content(self, item):
=======
                item = self.process_51job_content(item)
                self.save_to_mysql(spider, item)
            print(item)
        elif spider.name == "job_all":
            # item = self.process_job_all_content(item)
            self.save_to_mysql(spider, item)
            # print(item)
        return item

    # 对内容进行清洗
    def process_51job_content(self, item):
>>>>>>> a14f5150eb56dfc65bb96f2ac8b48776d600553f
        if "万/月" in item['salary']:
            item['salary_min'] = float(item['salary'].replace("万/月", "").split('-')[0]) * 10
            item['salary_max'] = float(item['salary'].replace("万/月", "").split('-')[1]) * 10
        elif "万/年" in item['salary']:

            item['salary_min'] = "%.2f" % (float(item['salary'].replace("万/年", "").split('-')[0]) / 12 * 10)
            item['salary_max'] = '%.2f' % (float(item['salary'].replace("万/年", "").split('-')[1]) / 12 * 10)
        elif "千/月" in item['salary']:
            item['salary_min'] = float(item['salary'].replace("千/月", "").split('-')[0])
            item['salary_max'] = float(item['salary'].replace("千/月", "").split('-')[1])
        elif "元/天" in item['salary']:
            item['salary_min'] = float(item['salary'].replace("元/天", "")) * 30 / 1000
            item['salary_max'] = float(item['salary'].replace("元/天", "")) * 30 / 1000
        elif "千以下/月" in item['salary']:
            item['salary_min'] = 0
            item['salary_max'] = float(item['salary'].replace("千以下/月", ""))
        elif "千以下/月" in item['salary']:
            item['salary_min'] = 0
            item['salary_max'] = float(item['salary'].replace("千以下/月", ""))
        elif "万以上/年" in item['salary']:
            item['salary_min'] = '%.2f' % (float(item['salary'].replace("万以上/年", "")) / 12 * 10)
            item['salary_max'] = 0
        else:
            item['salary_min'] = 0
            item['salary_max'] = 0
        item['salary'] = str(item['salary_min']) + '-' + str(item['salary_max']) + 'k'
        return item

<<<<<<< HEAD
=======
    def process_job_all_content(self, item):
        item['company'] = item['company'].strip()
        return item

    def process_lagou_content(self, item):
        return item

>>>>>>> a14f5150eb56dfc65bb96f2ac8b48776d600553f
    def open_spider(self, spider):
        # 建立连接
        self.conn = pymysql.connect(host=spider.settings.get("MYSQL_HOST"), db=spider.settings.get('MYSQL_DB'),
                                    user=spider.settings.get("MYSQL_USER"), passwd=spider.settings.get("MYSQL_PWD"))
        # 创建游标，操作设置为字典类型
        self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def close_spider(self, spider):
        self.cur.close()
        self.conn.close()

    # 保存数据
    def save_to_mysql(self, spider, item):
        if spider.name == '51job':
            try:
                sql = "insert into {}(companyFullName,area,location,positionName,Key_word,time,salary,salary_min,salary_max,url) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on duplicate key update companyFullName = values(companyFullName),positionName=values(positionName)".format(
                    spider.settings.get("MYSQL_TB"))
                print(type(item['salary_min']))
                # with mysql.DB(host=spider.settings.get("MYSQL_HOST"), user=spider.settings.get("MYSQL_USER"),
                #               passwd=spider.settings.get("MYSQL_PWD"), db='51job') as db:
                self.cur.execute(sql,
                                 (item['companyFullName'], item['location'].split('-')[0], item['location'],
                                  item['positionName'],
                                  item['Key_word'], item['time'],
                                  item['salary'], item['salary_min'], item['salary_max'], item['url']))
                self.conn.commit()
            except Exception as e:
                print(e)

        elif spider.name == "job_all":
            try:
<<<<<<< HEAD
                sql = "insert into {}( positionName,company,salary,location,release_time,work_exp,edu_background,company_nature,company_scale,url) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on duplicate key update company = values(company),positionName=values(positionName),salary=values(salary)".format(
=======
                sql = "insert into {}( positionName,company,salary,location,release_time,work_exp,edu_background,company_nature,company_scale,url) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on duplicate key update company = values(company),positionName=values(positionName)".format(
>>>>>>> a14f5150eb56dfc65bb96f2ac8b48776d600553f
                    spider.settings.get("MYSQL_TB"))
                # with mysql.DB(host=spider.settings.get("MYSQL_HOST"), user=spider.settings.get("MYSQL_USER"),
                #               passwd=spider.settings.get("MYSQL_PWD"), db='51job') as db:
                self.cur.execute(sql,
                                 (item['name'],item['company'],item['salary'], item['location'],
                                  item['release_time'], item['work_exp'],
                                  item['edu_background'], item['company_nature'], item['company_scale'], item['url']))
                self.conn.commit()
            except Exception as e:
                print(e)
<<<<<<< HEAD
        elif spider.name == 'job_c':
            try:
                sql = "insert into {}( positionName,company,salary,area,location,release_time,work_exp,edu_background,company_nature,company_scale,url,key_word,salary_min,salary_max) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) on duplicate key update key_word = values(key_word),release_time = values(release_time),work_exp=values(work_exp),edu_background=values(edu_background)".format(
                    spider.settings.get("MYSQL_TB"))
                # with mysql.DB(host=spider.settings.get("MYSQL_HOST"), user=spider.settings.get("MYSQL_USER"),
                #               passwd=spider.settings.get("MYSQL_PWD"), db='51job') as db:
                self.cur.execute(sql,
                                 (item['name'], item['company'], item['salary'],item['location'].split('-')[0], item['location'],
                                  item['release_time'], item['work_exp'],
                                  item['edu_background'], item['company_nature'], item['company_scale'], item['url'],item['key_word'],item['salary_min'],item['salary_max']))
                self.conn.commit()
            except Exception as e:
                print(e)
=======
>>>>>>> a14f5150eb56dfc65bb96f2ac8b48776d600553f
